DROP TABLE QVW_EKPOS
GO
CREATE TABLE QVW_EKPOS
AS
SELECT DISTINCT  EKPNEUD AS NDATE,SUBSTR(EKPNEUD,1,4) AS NYEAR,SUBSTR(EKPNEUD,5,2) AS NMONTH,
SUBSTR(EKPNEUD,7,2) AS NDAY,'Q'||CEIL(TO_NUMBER(SUBSTR(EKPNEUD,5,2))/3) AS NQUARTER,
'B'||EKPAUFKRS||EKPAUFLFD AS KAUF,'B' AS AUFTYP,'B'||EKPAUFKRS||EKPAUFLFD||EKPAUFPOS AS PAUF ,
EKPAUFKRS AS PKRS,EKPAUFLFD AS PLFD,EKPAUFPOS AS PPOS,EKPLIEFNR AS LSNR,EKPNEUB AS KSTAFF, EKPPOSTYP,
EKPWAKZ AS ARTST,utl_raw.cast_to_varchar2(EKPART) AS PART,utl_raw.cast_to_varchar2(EKPVAR) AS PVAR,
utl_raw.cast_to_varchar2(EKPFARBE) AS PCOLOR,EKPDESS AS PDESS,EKPCOL AS PDCWY,EKPBESCH AS PFINISH,
EKPGROE AS PSIZE,EKPMEEH,EKPPRME,EKPPREH,EKPPREHFKT,
ROUND(EKPME1*0.01*EKPMEEHFKT*0.000001,2) AS EKPME1,ROUND(EKPME1*0.01*EKPMEEHFKT*0.000001*EKPPRME*0.001,2) AS EKPWE1,
ROUND(EKPME3*0.01*EKPMEEHFKT*0.000001,2) AS EKPME3,
ROUND(EKPME3*0.01*EKPMEEHFKT*0.000001*EKPPRME*0.001,2) AS EKPWE3,ROUND(EKPPRME*0.001,2)  AS EKPPREIS,
ROUND(EKPME1*0.01*EKPMEEHFKT*0.000001*EKPPRME*0.001,2)  AS EKPVALUE ,
ROUND(EKPME1*0.01*EKPMEEHFKT*0.000001,2) AS EKPQTY,ROUND(EKPME2*0.01*EKPMEEHFKT*0.000001,2) AS QRECEIVE,
EKPLTERM AS DDAT,SUBSTR(EKPLTERM,1,4) AS DYEAR,SUBSTR(EKPLTERM,5,2) AS DMONTH,SUBSTR(EKPLTERM,7,2) AS DDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(EKPLTERM,5,2))/3) AS DQUARTER ,EKPDRUDAT AS DRDAT,
SUBSTR(EKPDRUDAT,1,4) AS DRYEAR,SUBSTR(EKPDRUDAT,5,2) AS DRMONTH,SUBSTR(EKPDRUDAT,7,2) AS DRDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(EKPDRUDAT,5,2))/3) AS DRQUARTER,EKPWVTERM AS VTERMDAT,SUBSTR(EKPWVTERM,1,4) AS VYEAR,
SUBSTR(EKPWVTERM,5,2) AS VMONTH,SUBSTR(EKPWVTERM,7,2) AS VDAY,EKPWUTERM AS UTERMDAT,SUBSTR(EKPWUTERM,1,4) AS UYEAR,
SUBSTR(EKPWUTERM,5,2) AS UMONTH,SUBSTR(EKPWUTERM,7,2) AS UDAY,EKPAEND AS RDATE,SUBSTR(EKPAEND,1,4) AS RYEAR,
SUBSTR(EKPAEND,5,2) AS RMONTH,SUBSTR(EKPAEND,7,2) AS RDAY,CASE  WHEN  EKPAEND>EKPWUTERM THEN 'N' ELSE 'Y' END AS EKOTD
FROM EKPOS WHERE EKPSA='PO' AND EKPMANDANT='00' AND EKPNEUD>0
GO

